package nc.bs.common.freereport;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;

import nc.bs.logging.Logger;
import nc.bs.mw.sqltrans.TempTable;

import nc.jdbc.framework.DataSourceCenter;
import nc.jdbc.framework.JdbcSession;
import nc.jdbc.framework.PersistenceManager;
import nc.jdbc.framework.SQLParameter;
import nc.jdbc.framework.crossdb.CrossDBConnection;
import nc.jdbc.framework.exception.DbException;
import nc.jdbc.framework.util.DBConsts;
import nc.jdbc.framework.util.DBUtil;

import nc.vo.am.common.TempData;
import nc.vo.am.common.util.ExceptionUtils;
import nc.vo.logging.Debug;
import nc.vo.pub.BusinessException;
import nc.vo.pub.SuperVO;
import nc.vo.pub.lang.UFBoolean;
import nc.vo.pub.lang.UFDouble;

/**
 * 构造临时表。
 *
 * @author mxh
 */
public class TempTableDAO {
    //被执行次数
    private int seq = 0;

    public static TempTableDAO getInstance() {
        return new TempTableDAO();
    }

    /**
     * 根据传入的数据，直接构造临时表。
     *
     * @param values
     * @return
     * @throws java.sql.SQLException
     */
    public TempData createTable(Object[] values) throws BusinessException {
        return createTable("temp_pam_pk"+getTempNameSuffix(), "pk", "varchar(20)", values);
    }

    /**
     * 创建指定长度的临时表。
     * @param values
     * @param idFieldLength
     * @return
     * @throws BusinessException
     * @author taorz1
     * @time 2011-11-14 下午01:08:05
     */
    public TempData createTable(Object[] values,int idFieldLength)throws BusinessException {
        // note by taorz1 要新建临时表，否则在oracle下，并不会将原有临时表删除，所以字段长度亦不会改变。
        return createTable("temp_pam_pk"+getTempNameSuffix()+idFieldLength, "pk", "varchar("+idFieldLength+")", values);
    }

    @SuppressWarnings("unchecked")
    public TempData createTable(String tableName, String columnName,
                                String columnType, Object[] values) throws BusinessException {
        List valuesList = Arrays.asList(values);
        return createTable(tableName, columnName, columnType, valuesList);
    }

    @SuppressWarnings("unchecked")
    public TempData createTable(String tableName, String columnName,
                                String columnType, List valuesList) throws BusinessException {
        TempData tempData = new TempData(tableName, columnName, columnType,
                valuesList);

        Connection con = null;
        JdbcSession session = getJdbcSession();
        try {
            con = session.getConnection();
            session.setAddTimeStamp(false);
            String tempTable = tableName;
            TempTable TempTableFactory = new TempTable();
//			if (isTableExist(tableName)) {
//				// 先删除，后增加。
//				session.executeUpdate("delete from " + tableName);
//			} else {
            // mxh 加时间戳
            tempTable = TempTableFactory.createTempTable(con, tableName, columnName + " " + columnType, new String[]{null});
            // 重置TS标识，否则在DB2中标识会重置，详见：nc.jdbc.framework.crossdb.temptable.DB2TempTableCreator
            session.setAddTimeStamp(false);
            // 设置统计信息
//				TempTableFactory.setRowsnum(valuesList.size());
//			}

            String sql_insert = "insert into " + tempTable + " (" + columnName
                    + ")  values(?) ";

            Iterator iter = valuesList.iterator();
            Object value = null;
            while (iter.hasNext()) {
                Object obj = iter.next();
                if (obj == null)
                    continue;
                value = obj;
                SQLParameter sqlParam = new SQLParameter();
                sqlParam.addParam(value);
                session.addBatch(sql_insert, sqlParam);
            }
            session.executeBatch();
            // mxh,因为创建完临时表名称发生改变，所以需要重新设置
            tempData.setTableName(tempTable);

        } catch (DbException e) {
            Logger.error(e.getMessage(), e);
            throw new BusinessException(e.getMessage());
        } catch (SQLException e) {
            Logger.error(e.getMessage(), e);
            throw new BusinessException(e.getMessage());
        } finally {
            // 为什么要捕捉异常，不会发生异常
//			try {
            if (session != null)
                session.closeAll();
//			} catch (Exception e) {
//			}
        }
        return tempData;

    }

    /**
     * 创建临时表，默认填加ts字段
     *
     * @param tableName
     * @param colNames
     * @param dataTypes
     * @return
     * @throws BusinessException
     */
    public String createTable(String tableName, String[] colNames,
                              String[] dataTypes) throws BusinessException {
        java.sql.Connection con = null;
        java.sql.PreparedStatement stmt = null;
        String tabname = null;
        JdbcSession session = getJdbcSession();
        try {
            con = session.getConnection();
            nc.bs.mw.sqltrans.TempTable tmpTool = new nc.bs.mw.sqltrans.TempTable();
            String colName1andDataType = "";
            String colName_insert = "";

            for (int i = 0; i < colNames.length; i++) {
                String temp = "";
                if (i > 0) {
                    temp += ",";
                    colName_insert += ",";

                }
                temp += colNames[i] + " " + dataTypes[i];
                colName1andDataType += temp;
                colName_insert += colNames[i];

            }
            colName1andDataType += ",ts varchar(19) ";
            tabname = tmpTool.createTempTable(con, tableName,
                    colName1andDataType, new String[]{null});
        } catch (SQLException e) {
            Logger.error(e.getMessage(), e);
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (Exception e) {
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
            }
        }
        return tabname;
    }

    /**
     * 创建临时表，默认填加ts字段
     *
     * @param tableName
     * @param colNames
     * @param dataTypes
     * @return
     * @throws BusinessException
     */
    public String createTableWithIndex(String tableName, String[] colNames,
                                       String[] dataTypes, String index) throws BusinessException {
        java.sql.Connection con = null;
        java.sql.PreparedStatement stmt = null;
        String tabname = null;
        JdbcSession session = getJdbcSession();
        try {
            con = session.getConnection();
            nc.bs.mw.sqltrans.TempTable tmpTool = new nc.bs.mw.sqltrans.TempTable();
            String colName1andDataType = "";
            String colName_insert = "";

            for (int i = 0; i < colNames.length; i++) {
                String temp = "";
                if (i > 0) {
                    temp += ",";
                    colName_insert += ",";

                }
                temp += colNames[i] + " " + dataTypes[i];
                colName1andDataType += temp;
                colName_insert += colNames[i];

            }
            tabname = tmpTool.createTempTable(con, tableName,
                    colName1andDataType, new String[]{index});
        } catch (SQLException e) {
            Logger.error(e.getMessage(), e);
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (Exception e) {
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
            }
        }
        return tabname;
    }

    /**
     * <p>
     * 创建临时表，默认填加ts字段
     *
     * @param tableName
     * @param colNames
     * @param dataTypes
     * @param indexs
     * @return
     * @throws BusinessException
     */
    public String createTableWithMultiIndexs(String tableName, String[] colNames,
                                             String[] dataTypes, String[] indexs) throws BusinessException {
        java.sql.Connection con = null;
        java.sql.PreparedStatement stmt = null;
        String tabname = null;
        JdbcSession session = getJdbcSession();
        try {
            con = session.getConnection();
            nc.bs.mw.sqltrans.TempTable tmpTool = new nc.bs.mw.sqltrans.TempTable();
            String colName1andDataType = "";
            String colName_insert = "";

            for (int i = 0; i < colNames.length; i++) {
                String temp = "";
                if (i > 0) {
                    temp += ",";
                    colName_insert += ",";
                }
                temp += colNames[i] + " " + dataTypes[i];
                colName1andDataType += temp;
                colName_insert += colNames[i];
            }
            tabname = tmpTool.createTempTable(con, tableName,
                    colName1andDataType, indexs);
        } catch (SQLException e) {
            Logger.error(e.getMessage(), e);
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (Exception e) {
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
            }
        }
        return tabname;
    }

    /**
     * 创建临时表，不填加ts字段
     *
     * @param tableName
     * @param colNames
     * @param dataTypes
     * @return
     * @throws BusinessException
     */
    public String createTableWithoutTS(String tableName, String[] colNames,
                                       String[] dataTypes) throws BusinessException {
        java.sql.Connection con = null;
        java.sql.PreparedStatement stmt = null;
        String tabname = null;
        JdbcSession session = getJdbcSession();
        try {
            con = session.getConnection();
            nc.bs.mw.sqltrans.TempTable tmpTool = new nc.bs.mw.sqltrans.TempTable();
            String colName1andDataType = "";
            String colName_insert = "";

            for (int i = 0; i < colNames.length; i++) {
                String temp = "";
                if (i > 0) {
                    temp += ",";
                    colName_insert += ",";

                }
                temp += colNames[i] + " " + dataTypes[i];
                colName1andDataType += temp;
                colName_insert += colNames[i];

            }
            tabname = tmpTool.createTempTable(con, tableName,
                    colName1andDataType, new String[]{null});
        } catch (SQLException e) {
            Logger.error(e.getMessage(), e);
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (Exception e) {
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
            }
        }
        return tabname;
    }

    /**
     * 根据SuperVO创建临时表
     * @param tableName
     * @param vo
     * @return
     * @throws BusinessException
     */
    public String createTable(String tableName, SuperVO vo) throws BusinessException {
        ArrayList<String> columnTypeList = new ArrayList<String>();
        String[] columnNames = vo.getAttributeNames();

        for (int i = 0; i < columnNames.length; i++) {
            String columnType = "varchar(200)";
            Object value = vo.getAttributeValue(columnNames[i]);
            if (value == null) {
                if(value instanceof UFDouble){
                    columnType = "decimal(30,13)";
                }
                if(value instanceof Integer){
                    columnType = "int";
                }
                if(value instanceof UFBoolean){
                    columnType = "char(1)";
                }
            }
            columnTypeList.add(columnType);
        }

        return  createTableWithoutTS(tableName, columnNames, columnTypeList.toArray(new String[0]));
    }


    public TempData createTable(String tableName, String[] columnNames,
                                String[] columnTypes, List<Object[]> values)
            throws BusinessException {
        String tn = createTable(tableName, columnNames, columnTypes);
        TempData tempData = insertIntoTable(tn, columnNames, columnTypes,
                values);
        return tempData;
    }

    /**
     * 向临时表中增加记录。
     *
     * @param tableName
     * @param colNames
     * @param valuesList
     * @return
     * @throws java.sql.SQLException
     * @throws DbException
     */
    public TempData insertIntoTable(String tableName, String[] colNames,
                                    String[] columnTypes, List<Object[]> valuesList)
            throws BusinessException {
        TempData tempData = new TempData(tableName, colNames, columnTypes,
                valuesList);
        java.sql.Connection con = null;
        JdbcSession session = getJdbcSession();
        try {
            con = session.getConnection();
            if (con instanceof CrossDBConnection) {
                ((CrossDBConnection) con).setAddTimeStamp(false);
            }
            String colName_insert = "";
            String value_insert = "";
            for (int i = 0; i < colNames.length; i++) {
                if (i > 0) {
                    colName_insert += ",";
                    value_insert += ",";
                }

                colName_insert += colNames[i];
                value_insert += "?";
            }

            String sql_insert = "insert into " + tableName + " ("
                    + colName_insert + ") values(" + value_insert + ") ";

            for (int i = 0; i < valuesList.size(); i++) {
                Object[] row = valuesList.get(i);

                SQLParameter sqlParam = new SQLParameter();
                for (int j = 0; j < row.length; j++) {
                    Object obj = row[j];
                    String value = (String) obj;
                    sqlParam.addParam(value);
                }
                session.addBatch(sql_insert, sqlParam);
            }
            session.executeBatch();
        } catch (DbException e) {
            Logger.error(e.getMessage(), e);
        } finally {
            try {
                if (session != null)
                    session.closeAll();
            } catch (Exception e) {
            }
        }

        return tempData;
    }

    public void dropTempTable(String tableName) throws BusinessException {
        if (tableName == null || tableName.trim().equals("")) {
            return;
        }
        String dropTableSql = "drop table " + tableName;
        java.sql.Connection con = null;
        java.sql.Statement stmt = null;
        JdbcSession session = getJdbcSession();
        try {
            con = session.getConnection();
            if (isOracle()) {
                return;
            }
            stmt = con.createStatement();
            stmt.execute(dropTableSql);
        } catch (Exception e) {
            ExceptionUtils.asBusinessException(e);
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (Exception e) {
                ExceptionUtils.asBusinessException(e);
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                ExceptionUtils.asBusinessException(e);
            }
        }
    }

    private JdbcSession getJdbcSession() {
        PersistenceManager manager = null;
        try {
            manager = PersistenceManager.getInstance();
        } catch (DbException e) {
            Logger.error(e.getMessage(), e);
        }
        return manager.getJdbcSession();
    }

    /**
     * 判断数据表是否存在
     *
     * @param tableName
     *            数据表名称
     * @return
     * @throws BusinessException
     *             出错抛出BusinessException
     */
    private boolean isTableExisted(String tableName) throws BusinessException {
        if (tableName == null)
            throw new NullPointerException("TableName is null!");
        PersistenceManager manager = null;
        ResultSet rs = null;
        try {
            manager = PersistenceManager.getInstance();
            int dbType = manager.getDBType();
            DatabaseMetaData dbmd = manager.getMetaData();

            // 获得表名信息
            rs = dbmd.getTables(manager.getCatalog(), manager.getSchema(),
                    getTableName(dbType, tableName), new String[] { "TABLE" });
            while (rs.next()) {
                return true;
            }
            return false;
        } catch (Exception e) {
            Logger.error(e.getMessage(), e);
            throw new BusinessException(e.getMessage());
        } finally {
            DBUtil.closeRs(rs);
            if (manager != null)
                manager.release();
        }
    }

    /**
     * 判断数据库表是否存在。
     *
     * @param tableName
     * @return
     */
    private boolean isTableExist(String tableName) {
        try {
            return isTableExisted(tableName);
        } catch (BusinessException e) {
            Debug.debug(e.getMessage(), e);
            return false;
        }
    }

    /**
     * 返回数据库相关的表名
     *
     * @param dbType
     *            int
     * @param tableName
     *            java.lang.String
     * @return java.lang.String
     * @since ：V1.00
     */
    protected String getTableName(int dbType, String tableName) {
        String strTn = null;
        switch (dbType) {
            case DBConsts.SQLSERVER:
                strTn = tableName;
                break;
            case DBConsts.ORACLE:
            case DBConsts.DB2:
                // ORACLE需将表名大写
                strTn = tableName.toUpperCase();
                break;
        }
        return strTn;
    }

    /**
     * 判断是否是oracle数据库。
     *
     * @return
     */
    private boolean isOracle() {
        return DBConsts.ORACLE == getDBType();
    }

    /**
     * 获得数据源类型
     *
     *            nc.vo.pub.oid.OID
     * @throws java.sql.SQLException
     *             异常说明。
     */
    private Integer getDBType() {
        return DataSourceCenter.getInstance().getDatabaseType(null);

    }

    private String getTempNameSuffix(){
        seq++;
        return seq>1 ? String.valueOf(seq):"";
    }
}
